SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE PROCEDURE [dbo].[usp_SearchCollegeAndSpecialityAboveZero]
    (
      @fdSpecType INT,--类别为下拉框选择，故不可能为空
      @fdSpecName VARCHAR(50),
      @fdCollName VARCHAR(50)
    )
AS 
    IF @fdSpecType = 1 
        SELECT  a.fdCollID,
                a.fdCollName,
                b.fdSpecID,
                b.fdSpecName,
				case							-- doo 20110215 增显专业类型
					when b.fdSpecType = 1 then '本科生'
					when b.fdSpecType = 2 then '研究生'
				end,
				c.fdSpecCampus,
                c.fdSpecBachelorNumber,			-- doo 20110215 细化人数为各学历
				c.fdSpecMasterNumber,			-- doo 20110215 细化人数为各学历
				c.fdSpecDoctorNumber			-- doo 20110215 细化人数为各学历
        FROM    FS_College AS a,
                FS_Speciality AS b,
                JOL_SpecialityIntroduction AS c
        WHERE   b.fdSpecType = @fdSpecType
                AND ( a.fdCollName LIKE '%' + @fdCollName + '%' )
                AND ( b.fdSpecName LIKE '%' + @fdSpecName + '%' )
                AND b.fdCollID = a.fdCollID
                AND c.fdSpecID = b.fdSpecID
				-- doo 20110215
                AND (c.fdSpecBachelorNumber > 0 or c.fdSpecMasterNumber > 0 or c.fdSpecDoctorNumber > 0)
        ORDER BY b.fdCollID,
                c.fdSpecID
    ELSE 
        IF @fdSpecType = 2 
            SELECT  a.fdCollID,
                    a.fdCollName,
                    b.fdSpecID,
                    b.fdSpecName,
					case							-- doo 20110215 增显专业类型
						when b.fdSpecType = 1 then '本科生'
						when b.fdSpecType = 2 then '研究生'
					end,
					c.fdSpecCampus,
					c.fdSpecBachelorNumber,			-- doo 20110215 细化人数为各学历
					c.fdSpecMasterNumber,			-- doo 20110215 细化人数为各学历
					c.fdSpecDoctorNumber			-- doo 20110215 细化人数为各学历
            FROM    FS_College AS a,
                    FS_Speciality AS b,
                    JOL_SpecialityIntroduction AS c
            WHERE   b.fdSpecType = @fdSpecType
                    AND ( a.fdCollName LIKE '%' + @fdCollName + '%' )
                    AND ( b.fdSpecName LIKE '%' + @fdSpecName + '%' )
                    AND b.fdCollID = a.fdCollID
                    AND c.fdSpecID = b.fdSpecID
					-- doo 20110215
					AND (c.fdSpecBachelorNumber > 0 or c.fdSpecMasterNumber > 0 or c.fdSpecDoctorNumber > 0)
            ORDER BY b.fdCollID,
                    c.fdSpecID
    ELSE 
        IF @fdSpecType = 3 
            SELECT  a.fdCollID,
                    a.fdCollName,
                    b.fdSpecID,
                    b.fdSpecName,
					case							-- doo 20110215 增显专业类型
						when b.fdSpecType = 1 then '本科生'
						when b.fdSpecType = 2 then '研究生'
					end,
					c.fdSpecCampus,
					c.fdSpecBachelorNumber,			-- doo 20110215 细化人数为各学历
					c.fdSpecMasterNumber,			-- doo 20110215 细化人数为各学历
					c.fdSpecDoctorNumber			-- doo 20110215 细化人数为各学历
            FROM    FS_College AS a,
                    FS_Speciality AS b,
                    JOL_SpecialityIntroduction AS c
            WHERE   ( a.fdCollName LIKE '%' + @fdCollName + '%' )
                    AND ( b.fdSpecName LIKE '%' + @fdSpecName + '%' )
                    AND b.fdCollID = a.fdCollID
                    AND c.fdSpecID = b.fdSpecID
					-- doo 20110215
					AND (c.fdSpecBachelorNumber > 0 or c.fdSpecMasterNumber > 0 or c.fdSpecDoctorNumber > 0)
            ORDER BY b.fdCollID,
                    c.fdSpecID




GO
